/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package modele;

import exceptions.EmployeNotFoundException;
import exceptions.LoginException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author julientrebern
 */
public class EmployeDB implements InterfaceEmploye{

    private Connection connection;
    private Statement stmt;
    private ResultSet rs;

    public EmployeDB() {
    }

    ;
    
    public Employe getEmploye(String username) throws SQLException, EmployeNotFoundException {

        Employe employe = null;
        rs = stmt.executeQuery("select * from employes where username=\'"
                + username + "'");

        if (rs.next()) {


            employe = new Employe();
            employe.setUsername(rs.getString("username"));
            employe.setPassword(rs.getString("password"));
            employe.setDepid(Integer.parseInt(rs.getString("depid")));
            employe.setRoleid(Integer.parseInt(rs.getString("roleid")));
            employe.setName(rs.getString("name"));
            employe.setPhone(rs.getString("phone"));
            employe.setEmail(rs.getString("email"));

        } else {
            throw new EmployeNotFoundException("Employe " + username + " non trouve!");
        }
        return employe;

    }

    public void createEmploye(Employe employe) throws SQLException {


        StringBuffer sqlString =
                new StringBuffer("insert into employes ");

        sqlString.append("values ('"
                + employe.getUsername() + "', ");
        sqlString.append("'"
                + employe.getPassword() + "', ");
        sqlString.append("'"
                + employe.getName() + "', ");
        sqlString.append(
                employe.getRoleid() + ", ");
        sqlString.append("'"
                + employe.getPhone() + "', ");
        sqlString.append("'"
                + employe.getEmail() + "', ");
        sqlString.append(
                employe.getDepid() + ")");

        stmt.execute(sqlString.toString());

    }

    public List<Employe> getAllEmployes() throws SQLException {



        rs = stmt.executeQuery("select * from employes, roles, "
                + "services where employes.roleid=roles.roleid "
                + "and employes.depid=services.depid");

        Employe employe;
        List employes = new ArrayList<Employe>();

        while (rs.next()) {

            employe = new Employe();

            employe.setUsername(rs.getString("username"));
            employe.setName(rs.getString("name"));
            employe.setRolename(rs.getString("rolename"));
            employe.setPhone(rs.getString("phone"));
            employe.setEmail(rs.getString("email"));
            employe.setRoleid(new Integer(rs.getInt("roleid")));
            employe.setDepid(new Integer(rs.getInt("depid")));
            employe.setDepartment(rs.getString("depname"));

            employes.add(employe);
        }
        return employes;
    }

    public void deleteEmploye(String username) throws SQLException {

        StringBuffer sqlString =
                new StringBuffer("delete from employes ");
        sqlString.append("where username='" + username + "'");

        stmt.execute(sqlString.toString());
    }

    public void updateEmploye(Employe employe) throws SQLException {


        StringBuffer sqlString =
                new StringBuffer("update employes ");
        

        sqlString.append("set password='"
                + employe.getPassword() + "', ");
        sqlString.append("roleid="
                + String.valueOf(employe.getRoleid()) + ", ");
        sqlString.append("name='"
                + employe.getName() + "', ");
        sqlString.append("phone='"
                + employe.getPhone() + "', ");
        sqlString.append("email='"
                + employe.getEmail() + "', ");
        sqlString.append("depid="
                + String.valueOf(employe.getDepid()));
        sqlString.append(" where username='"
                + employe.getUsername() + "'");

        System.err.println("tessst : "+sqlString.toString());
        stmt.execute(sqlString.toString());
    }

    public String login(String username, String password) throws SQLException, LoginException {

        rs = stmt.executeQuery("select * from employes where username=\'"
                + username + "' "
                + "and password='" + password + "'");

        String user = null;
        if (rs.next()) {

            user = rs.getString("username");

        } else {
            throw new LoginException();
        }

        return user;
    }

    public void connect(Connection conn) throws SQLException {

        this.connection = conn;
        this.stmt = conn.createStatement();
    }
}
